import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Sort;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * Spring Data JPA Specification 测试
 *
 * @author luohq
 * @date 2022-07-19 15:41
 * @see org.springframework.data.jpa.repository.support.SimpleJpaRepository#applySpecificationToCriteria
 */
public class MyDataRepositorySpecificationTest extends BaseTest {


    private static final Logger log = LoggerFactory.getLogger(MyDataRepositorySpecificationTest.class);

    @Resource
    private MyDataRepository myDataRepository;

    @Test
    void testSpecification_selectOrderBy() {
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where my_type >= 1
         * order by create_time desc, my_type asc
         */
        List<MyData> resultList = this.myDataRepository.findAll((root, cq, cb) -> {
            cq
                    //此处cq.multiselect不好用，后续会被覆盖为cq.select(root)
                    //参见org.springframework.data.jpa.repository.support.SimpleJpaRepository#applySpecificationToCriteria
                    .multiselect(root.get("id"), root.get("myName"), root.get("myType"))
                    //order by生效
                    .orderBy(cb.desc(root.get("createTime")), cb.asc(root.get("myType")));
            //构造Predicate
            return cb.greaterThanOrEqualTo(root.get("myType"), 1);
        });
        //MyData singleResult = query.getSingleResult();
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    @Test
    void testSpecification_selectOrderBySort() {
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where my_type >= 1
         * order by create_time desc, my_type asc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    //构造Predicate
                    return cb.greaterThanOrEqualTo(root.get("myType"), 1);
                },
                Sort.by("createTime").descending().and(Sort.by("myType").ascending())
        );
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    @Test
    void testSpecification_selectCount() {
        /**
         * select count(id)
         * from my_data
         * where my_type >= 1
         */
        Long countResult = this.myDataRepository.count((root, cq, cb) -> {
            //构造Predicate
            return cb.greaterThanOrEqualTo(root.get("myType"), 1);
        });
        log.info("myData count: {}", countResult);
        Assertions.assertEquals(2, countResult);
    }

    @Test
    void testSpecification_queryWhereCombination() {
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where my_type >= 1 and (id = 1 or my_name like '%lu%')
         * order by create_time desc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    Predicate idPredicate = cb.equal(root.get("id"), 1);
                    Predicate myNamePredicate = cb.like(root.get("myName"), JpaUtil.like("lu"));
                    Predicate orPredicate = cb.or(idPredicate, myNamePredicate);

                    Predicate myTypePredicate = cb.greaterThanOrEqualTo(root.get("myType"), 1);
                    Predicate andPredicate = cb.and(myTypePredicate, orPredicate);
                    return andPredicate;
                },
                Sort.by("createTime").descending());
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testSpecification_queryWhereDynamicConjunction() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * and my_name like '%lu%'
         * and my_type = 1
         * and create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    Predicate conjunctionPredicate = cb.conjunction();
                    List<Expression<Boolean>> expressions = conjunctionPredicate.getExpressions();
                    if (Objects.nonNull(myDataQueryDto.getId())) {
                        expressions.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
                    }
                    if (StringUtils.hasText(myDataQueryDto.getMyName())) {
                        expressions.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
                    }
                    if (Objects.nonNull(myDataQueryDto.getMyType())) {
                        expressions.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
                        expressions.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
                        expressions.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
                    }
                    return conjunctionPredicate;
                },
                Sort.by("createTime").descending()
        );
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testSpecification_queryWhereDynamicDisjunction() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * or my_name like '%lu%'
         * or my_type = 1
         * or create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    Predicate disjunctionPredicate = cb.disjunction();
                    List<Expression<Boolean>> expressions = disjunctionPredicate.getExpressions();
                    if (Objects.nonNull(myDataQueryDto.getId())) {
                        expressions.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
                    }
                    if (StringUtils.hasText(myDataQueryDto.getMyName())) {
                        expressions.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
                    }
                    if (Objects.nonNull(myDataQueryDto.getMyType())) {
                        expressions.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
                        expressions.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
                        expressions.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
                    }
                    return disjunctionPredicate;
                },
                Sort.by("createTime").descending()
        );
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }


    @Test
    void testSpecification_queryWhereDynamicAnd() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * and my_name like '%lu%'
         * and my_type = 1
         * and create_time >= '2022-01-01 18:00:20' and create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    List<Predicate> predicates = new ArrayList<>(5);
                    if (Objects.nonNull(myDataQueryDto.getId())) {
                        predicates.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
                    }
                    if (StringUtils.hasText(myDataQueryDto.getMyName())) {
                        predicates.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
                    }
                    if (Objects.nonNull(myDataQueryDto.getMyType())) {
                        predicates.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
                        predicates.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
                        predicates.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
                    }
                    Predicate andPredicate = cb.and(predicates.stream().toArray(Predicate[]::new));
                    return andPredicate;
                },
                Sort.by("createTime").descending()
        );
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(1, resultList.size());
    }

    @Test
    void testSpecification_queryWhereDynamicOr() {
        MyDataQueryDto myDataQueryDto = this.buildMyDataQueryDto();
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where
         * id = 1
         * or my_name like '%lu%'
         * or my_type = 1
         * or create_time >= '2022-01-01 18:00:20' or create_time <= '2122-01-01 18:00:20'
         * order by create_time desc
         */
        List<MyData> resultList = this.myDataRepository.findAll(
                (root, cq, cb) -> {
                    List<Predicate> predicates = new ArrayList<>(5);
                    if (Objects.nonNull(myDataQueryDto.getId())) {
                        predicates.add(cb.equal(root.get("id"), myDataQueryDto.getId()));
                    }
                    if (StringUtils.hasText(myDataQueryDto.getMyName())) {
                        predicates.add(cb.like(root.get("myName"), JpaUtil.like(myDataQueryDto.getMyName())));
                    }
                    if (Objects.nonNull(myDataQueryDto.getMyType())) {
                        predicates.add(cb.equal(root.get("myType"), myDataQueryDto.getMyType()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeStart())) {
                        predicates.add(cb.greaterThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeStart()));
                    }
                    if (Objects.nonNull(myDataQueryDto.getCreateTimeEnd())) {
                        predicates.add(cb.lessThanOrEqualTo(root.get("createTime"), myDataQueryDto.getCreateTimeEnd()));
                    }
                    Predicate orPredicate = cb.or(predicates.stream().toArray(Predicate[]::new));
                    return orPredicate;
                },
                Sort.by("createTime").descending()
        );
        log.info("myDataList size: {}, data: {}", resultList.size(), resultList);
        Assertions.assertEquals(2, resultList.size());
    }

    private MyDataQueryDto buildMyDataQueryDto() {
        MyDataQueryDto myDataQueryDto = new MyDataQueryDto();
        myDataQueryDto.setId(1L);
        myDataQueryDto.setMyName("lu");
        myDataQueryDto.setMyType(2);
        myDataQueryDto.setCreateTimeStart(DateUtil.stringToDate("2022-01-01 18:00:20"));
        myDataQueryDto.setCreateTimeEnd(new Date());
        return myDataQueryDto;
    }

    @Test
    void tesSpecification_not() {
        /**
         * select id, my_name, my_type, create_time, create_by
         * from my_data
         * where my_type not in (1, 2) and my_type >=3
         */
        this.myDataRepository.findAll(((root, query, criteriaBuilder) -> {
            return criteriaBuilder.or(
                    criteriaBuilder.in(root.get("myType")).value(1).value(2),
                    criteriaBuilder.lessThan(root.get("myType"), 3)
            ).not();
        }));
    }

}
